USE onExam;
GO

IF OBJECT_ID('uspImportCenter', 'P') IS NOT NULL 
    DROP PROC dbo.uspImportCenter;
GO

CREATE PROCEDURE uspImportCenter
    (
      @Center AS CenterTableType READONLY
    )
/*********************
Proc:uspImportCenter
Use:批量导入指挥中心
Creator:Mark Yao
Create Date:2012-04-22
INPUT:

OUTPUT:

Tables:
1.dbo.Center  所属指挥中心

Returns:
-1:要插入的指挥中心有重名
0：没有导入任何行
   返回成功插入记录数
e.g
	DECLARE @Center AS CenterTableType;
	INSERT INTO @Center(CenterId,Name)
	VALUES (0,N'南京'),(0,N'广州'),(0,N'北京');
	EXEC uspImportCenter @Center;
 
**********************/
AS 
    BEGIN
        SET NOCOUNT ON;
        DECLARE @flag INT;
        DECLARE @qty INT;
  
        BEGIN TRAN   
    -- 检查是否有重名的指挥中心名称
        IF EXISTS ( SELECT  b.NAME
                    FROM    Center a
                            RIGHT JOIN @Center b ON a.Name = b.NAME
                    WHERE   a.Name IS NULL
                    GROUP BY b.NAME
                    HAVING  COUNT(b.NAME) > 1 ) 
            SET @flag = -1;
        ELSE 
            BEGIN
                MERGE INTO dbo.Center AS t
                    USING @Center AS s
                    ON t.Name = s.NAME
                    WHEN NOT MATCHED AND ISNULL(s.NAME, '') <> ''
                        THEN INSERT ( Name )
                         VALUES     ( s.NAME
                                    );
                SELECT  @flag = @@ROWCOUNT;   	
            END
            
  
        IF @@ERROR <> 0 
            BEGIN
                ROLLBACK;
                SET @flag = 0;
            END    
        ELSE 
            COMMIT;
  
  -- 返回结果
        SELECT  @flag Flag;
        SET NOCOUNT OFF;
    END

GO

